USE Partitioning;
GO

-- create an index
CREATE NONCLUSTERED INDEX IX_Customers_CustomerName ON dbo.Customers
(
	custname ASC
)
ON [PRIMARY];
GO

-- verify filegroup location of the index
SELECT
	t.name AS table_name,
	i.name AS index_name,
	i.type_desc,
    i.data_space_id,
	f.name AS [filegroup_name]
FROM sys.indexes AS i
    JOIN sys.filegroups AS f ON i.data_space_id = f.data_space_id
    JOIN sys.tables as t ON i.object_id = t.object_id
        AND i.object_id = OBJECT_ID(N'dbo.Customers','U');

-- move an existing nonclustered index to another filegroup
CREATE NONCLUSTERED INDEX IX_Customers_CustomerName ON dbo.Customers
(
	custname ASC
)
WITH (DROP_EXISTING = ON)
ON [SECONDARY];
GO

-- verify filegroup location of the index again